﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlServerCe;
using System.Configuration;
using System.Collections;

namespace SWEN3WebService.Models
{
    public class RoomDBManager
    {
        public static ArrayList GetAllRooms()
        {
            ArrayList result = new ArrayList();
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Rooms";
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Rooms r = new Rooms();
                    r.RoomNo = (int)dr["RoomNo"];
                    r.RoomRates = (double)dr["RoomRates"];
                    r.RoomStatus = (String)dr["RoomStatus"];
                    r.RoomType = (String)dr["RoomType"];
                    r.CheckIn = (DateTime)(dr["CheckIn"] != DBNull.Value ? dr["CheckIn"] : DateTime.MinValue);
                    r.CheckOut = (DateTime)(dr["CheckOut"] != DBNull.Value ? dr["CheckOut"]: DateTime.MinValue);
                    r.BookingNo = (int)(dr["BookingNo"] != DBNull.Value ? dr["BookingNo"]: 0);
                    r.StaffId = (int)(dr["StaffId"] != DBNull.Value ? dr["StaffId"] : 0);
                    result.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }


        public static Boolean updateRooms(Rooms r)
        {
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Rooms SET RoomType=@RoomType,RoomStatus=@RoomStatus, RoomRates=@RoomRates, CheckIn=@CheckIn, CheckOut=@CheckOut, BookingNo=@BookingNo, StaffId=@StaffId WHERE RoomNo=@RoomNo";
                comm.Parameters.AddWithValue("@RoomType", r.RoomType);
                comm.Parameters.AddWithValue("@RoomStatus", r.RoomStatus);
                comm.Parameters.AddWithValue("@RoomRates", r.RoomRates);
                comm.Parameters.AddWithValue("@CheckIn", r.CheckIn);
                comm.Parameters.AddWithValue("@CheckOut", r.CheckOut);
                comm.Parameters.AddWithValue("@BookingNo", r.BookingNo);
                comm.Parameters.AddWithValue("@StaffId", r.StaffId);
                if (comm.ExecuteNonQuery() == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}